package celcatSynchronizer;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStreamReader;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import org.sqlite.SQLiteConfig;

/**
 * Feed the geek inside...
 * Feed the database inside...
 * A simple database feeder from a CSV file.
 */
public class DbCreator
{
	/**
	 * Créé une base de donnée SQLite en mémoire et en retourne la connection.
	 * @return La connection à la nouvelle base de données.
	 */
	public Connection createDb() throws IOException, SQLException
	{
		CelcatSynchronizer.log.debug("Creating SQLite database");

		SQLiteConfig config = new SQLiteConfig();
		config.enforceForeignKeys(true);
		Connection db = DriverManager.getConnection("jdbc:sqlite::memory:", config.toProperties());
		Statement stat = db.createStatement();
		
		BufferedReader in = new BufferedReader(new InputStreamReader(getClass().getResourceAsStream("/celcatSynchronizer/resources/db.sql")));
		StringBuilder buffer = new StringBuilder();
		String line = in.readLine();
		while(line != null)
		{
			line = line.trim();
			buffer.append(line);
			
			if(line.endsWith(";"))
			{
				stat.execute(buffer.toString());
				buffer = new StringBuilder();
			}
			
			line = in.readLine();
		}
		
		return db;
	}
	
	/**
	 * Fonction utilitaire de split d'une ligne CSV.
	 * Les champs d'une ligne CSV sont séparés par une virgule. Néanmoins, un champ comportant une
	 * virgule est entouré de guillemets doubles. Cette fonction prend aussi en compte les antislash
	 * présents devant certains caractères de commande (", ', ...).
	 * @param p_line La ligne à split.
	 * @return La ligne splittée.
	 */
	private ArrayList<String> splitCsvLine(String p_line)
	{
		ArrayList<String> list = new ArrayList<String>();
		StringBuilder buffer = new StringBuilder();
		boolean quoted = false;
		
		for(int i = 0, end = p_line.length() ; i < end ; ++i)
		{
			char c = p_line.charAt(i);
			if(c == '\\' && i + 1 < end)
			{
				++i;
				buffer.append(p_line.charAt(i));
			}
			else if(!quoted)
			{
				if(c == ',')
				{
					list.add(buffer.toString());
					buffer = new StringBuilder();
				}
				else if(c == '"')
					quoted = true;
				else
					buffer.append(c);
			}
			else
			{
				if(c == '"')
					quoted = false;
				else
					buffer.append(c);
			}
				
		}
		
		list.add(buffer.toString());
		return list;
	}
	
	/**
	 * Parse d'un integer.
	 * @param p_value La valeur String.
	 * @return La valeur int.
	 */
	private int parseInteger(String p_value)
	{
		if(p_value.isEmpty())
			return 0;
		
		return Integer.parseInt(p_value);
	}
	
	/**
	 * Remplissage de la table CT_MODULE.
	 * @param p_stat La requète de remplissage.
	 * @param p_data Les données de remplissage.
	 */
	private void feedTableModule(PreparedStatement p_stat, ArrayList<String> p_data) throws IOException, SQLException
	{
		p_stat.setInt(1, Integer.parseInt(p_data.get(0)));
		p_stat.setString(2, p_data.get(1));
		p_stat.setString(3, p_data.get(2));
		p_stat.setString(4, p_data.get(3));
		p_stat.setString(5, p_data.get(4));
		p_stat.setString(6, p_data.get(5));
		p_stat.setString(7, p_data.get(6));
		p_stat.setString(8, p_data.get(7));
		p_stat.setString(9, p_data.get(8));
		p_stat.setString(10, p_data.get(9));
		p_stat.setString(11, p_data.get(10));
		p_stat.setString(12, p_data.get(11));
		p_stat.setString(13, p_data.get(12));
		p_stat.setString(14, p_data.get(13));
		p_stat.execute();
	}
	
	/**
	 * Remplissage de la table CT_ROOM.
	 * @param p_stat La requète de remplissage.
	 * @param p_data Les données de remplissage.
	 */
	private void feedTableRoom(PreparedStatement p_stat, ArrayList<String> p_data) throws IOException, SQLException
	{
		p_stat.setInt(1, Integer.parseInt(p_data.get(0)));
		p_stat.setString(2, p_data.get(1));
		p_stat.setString(3, p_data.get(2));
		p_stat.setString(4, p_data.get(3));
		p_stat.setString(5, p_data.get(4));
		p_stat.setString(6, p_data.get(5));
		p_stat.setString(7, p_data.get(6));
		p_stat.setString(8, p_data.get(7));
		p_stat.setString(9, p_data.get(8));
		p_stat.setString(10, p_data.get(9));
		p_stat.setString(11, p_data.get(10));
		p_stat.setString(12, p_data.get(11));
		p_stat.setString(13, p_data.get(12));
		p_stat.setInt(14, parseInteger(p_data.get(13)));
		p_stat.setString(15, p_data.get(14));
		p_stat.setString(16, p_data.get(15));
		p_stat.setString(17, p_data.get(16));
		p_stat.setString(18, p_data.get(17));
		p_stat.setString(19, p_data.get(18));
		p_stat.setInt(20, parseInteger(p_data.get(19)));
		p_stat.setInt(20, parseInteger(p_data.get(20)));
		p_stat.setString(22, p_data.get(21));
		p_stat.setString(23, p_data.get(22));
		p_stat.setString(24, p_data.get(23));
		p_stat.setString(25, p_data.get(24));
		p_stat.setString(26, p_data.get(25));
		p_stat.setString(27, p_data.get(26));
		p_stat.setString(28, p_data.get(27));
		p_stat.execute();
	}
	
	/**
	 * Remplissage de la table CT_GROUP.
	 * @param p_stat La requète de remplissage.
	 * @param p_data Les données de remplissage.
	 */
	private void feedTableGroup(PreparedStatement p_stat, ArrayList<String> p_data) throws IOException, SQLException
	{
		p_stat.setInt(1, Integer.parseInt(p_data.get(0)));
		p_stat.setString(2, p_data.get(1));
		p_stat.setString(3, p_data.get(2));
		p_stat.setString(4, p_data.get(3));
		p_stat.setString(5, p_data.get(4));
		p_stat.setString(6, p_data.get(5));
		p_stat.setString(7, p_data.get(6));
		p_stat.setString(8, p_data.get(7));
		p_stat.setString(9, p_data.get(8));
		p_stat.setString(10, p_data.get(9));
		p_stat.setString(11, p_data.get(10));
		p_stat.setString(12, p_data.get(11));
		p_stat.setInt(13, parseInteger(p_data.get(12)));
		p_stat.setString(14, p_data.get(13));
		p_stat.setString(15, p_data.get(14));
		p_stat.execute();
	}
	
	/**
	 * Fonction de parse du jour de la semaine.
	 * @param p_day Le jour de la semaine.
	 * @return La valeur int associée.
	 */
	private int parseDay(String p_day)throws IOException
	{
		if(p_day.equals("Mon"))
			return 1;
		else if(p_day.equals("Tue"))
			return 2;
		else if(p_day.equals("Wed"))
			return 3;
		else if(p_day.equals("Thu"))
			return 4;
		else if(p_day.equals("Fri"))
			return 5;
		else if(p_day.equals("Sat"))
			return 6;
		else if(p_day.equals("Sun"))
			return 7;
		else throw new IOException("No Week found");
	}
	
	/**
	 * Remplissage de la table CT_EVENT et associées.
	 * @param p_stat La requète de remplissage.
	 * @param p_data Les données de remplissage.
	 */
	private void feedTableEvent(PreparedStatement p_stat, ArrayList<String> p_data) throws IOException, SQLException
	{
		p_stat.setInt(1, Integer.parseInt(p_data.get(0)));
		p_stat.setInt(2, parseDay(p_data.get(1)));
		
		String[] startTime = p_data.get(2).split(":");
		p_stat.setInt(3, Integer.parseInt(startTime[0]) * 60 + Integer.parseInt(startTime[1]));
		
		String[] endTime = p_data.get(3).split(":");
		p_stat.setInt(4, Integer.parseInt(endTime[0]) * 60 + Integer.parseInt(endTime[1]));
		
		p_stat.setInt(5, parseInteger(p_data.get(4)));
		p_stat.setString(6, p_data.get(5));
		p_stat.setString(7, p_data.get(6));
		p_stat.setString(8, p_data.get(7));
		p_stat.setString(9, p_data.get(8));
		p_stat.setInt(10, parseInteger(p_data.get(9)));
		p_stat.setString(11, p_data.get(10));
		p_stat.setString(12, p_data.get(11));
		p_stat.setString(13, p_data.get(12));
		p_stat.setString(14, p_data.get(13));
		p_stat.setString(15, p_data.get(14));
		p_stat.setString(16, p_data.get(15));
		p_stat.setString(17, p_data.get(16));
		p_stat.execute();
	}
	
	/**
	 * Remplissage des tables CT_EVENT_*.
	 * @param p_stat La requète de remplissage.
	 * @param p_data Les données de remplissage.
	 */
	private void feedTableEventResource(PreparedStatement p_stat, ArrayList<String> p_data) throws IOException, SQLException
	{
		p_stat.setInt(1, Integer.parseInt(p_data.get(0)));
		p_stat.setString(2, p_data.get(1));
		p_stat.setString(3, p_data.get(2));
		p_stat.setString(4, p_data.get(3));
		p_stat.setString(5, p_data.get(4));
		p_stat.setInt(6, parseInteger(p_data.get(5)));
		p_stat.execute();
	}
	
	/**
	 * Fonction de parse d'une table du fichier CSV.
	 * @param p_stat La requète de feed.
	 * @param p_in Le fichier CSV.
	 * @param p_feedMethod La méthode de feed correspondante.
	 */
	private void parseCsvTable(PreparedStatement p_stat, BufferedReader p_in, Method p_feedMethod) throws IOException, SQLException, Exception
	{
		// Pour chaque ligne, on feed la table qui va bien.
		String line = p_in.readLine();
		while(!line.isEmpty())
		{
			try
			{
				p_feedMethod.invoke(this, p_stat, splitCsvLine(line));
				line = p_in.readLine();
			}
			catch(InvocationTargetException ex)
			{
				throw (Exception)ex.getTargetException();
			}
		}
	}
	
	/**
	 * Fonction de parse de la table CT_EVENT du fichier CSV (cas particulier).
	 * @param p_db La connexion à la db.
	 * @param p_in Le fichier CSV.
	 */
	private void parseCsvTableEvent(Connection p_db, BufferedReader p_in) throws IOException, SQLException
	{
		PreparedStatement statEvent = p_db.prepareStatement("INSERT INTO CT_EVENT (_event_id, _day_of_week, _start_time, _end_time, _break_mins, _spanName, _event_catName, _tag1, _tag2, _capacity_req, _deptName, _global_event, _protected, _suspended, _grouping_id, _registers_req, _lock) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
		PreparedStatement statEventModule = p_db.prepareStatement("INSERT INTO CT_EVENT_MODULE (_event_id, _module_id, _resWeeks, _layoutName, _scheduling_status, _quantity) VALUES (?, (SELECT _module_id FROM CT_MODULE WHERE _unique_name = ?), ?, ?, ?, ?)");
		PreparedStatement statEventGroup = p_db.prepareStatement("INSERT INTO CT_EVENT_GROUP (_event_id, _group_id, _resWeeks, _layoutName, _scheduling_status, _quantity) VALUES (?, (SELECT _group_id FROM CT_GROUP WHERE _unique_name = ?), ?, ?, ?, ?)");
		PreparedStatement statEventRoom = p_db.prepareStatement("INSERT INTO CT_EVENT_ROOM (_event_id, _room_id, _resWeeks, _layoutName, _scheduling_status, _quantity) VALUES (?, (SELECT _room_id FROM CT_ROOM WHERE _unique_name = ?), ?, ?, ?, ?)");
		PreparedStatement statStaff = p_db.prepareStatement("INSERT OR IGNORE INTO CT_STAFF (_unique_name) VALUES (?)");
		PreparedStatement statEventStaff = p_db.prepareStatement("INSERT INTO CT_EVENT_STAFF (_event_id, _staff_id, _resWeeks, _layoutName, _scheduling_status, _quantity) VALUES (?, (SELECT _staff_id FROM CT_STAFF WHERE _unique_name = ?), ?, ?, ?, ?)");
		PreparedStatement statEventWeek = p_db.prepareStatement("INSERT INTO CT_EVENT_WEEK (_event_id, _week) VALUES (?, ?)");
		
		// Pour chaque ligne commencant par un id, on ajoute l'event.
		// Pour chaque event, on ajoute dans CT_EVENT_WEEK les semaines de pop de l'event.
		// Pour chaque ligne sans id, on ajoute la ressource associée à l'event.
		String line = p_in.readLine();
		while(!line.isEmpty())
		{
			ArrayList<String> eventData = splitCsvLine(line.substring(0, line.length()));

			String eventWeeksLine = eventData.remove(4);
			feedTableEvent(statEvent, eventData);
			
			String[] eventWeeks = eventWeeksLine.split(",");
			for(int i = 0 ; i < eventWeeks.length ; ++i)
			{
				if(eventWeeks[i].indexOf("-") == -1)
				{
					statEventWeek.setInt(1, Integer.parseInt(eventData.get(0)));
					statEventWeek.setInt(2, Integer.parseInt(eventWeeks[i]));
					statEventWeek.execute();
				}
				else
				{
					String[] eventWeeksInterval = eventWeeks[i].split("-");
					Integer start = Integer.parseInt(eventWeeksInterval[0]);
					Integer end = Integer.parseInt(eventWeeksInterval[1]);
					for(int j = start ; j <= end ; ++j)
					{
						statEventWeek.setInt(1, Integer.parseInt(eventData.get(0)));
						statEventWeek.setInt(2, j);
						statEventWeek.execute();
					}
				 }
			}
			
			line = p_in.readLine();
			while(line.startsWith(","))
			{
				ArrayList<String> eventResource = splitCsvLine(line.substring(18));
				String resourceType = eventResource.remove(0);
				eventResource.add(0, eventData.get(0));
				if(resourceType.equals("Module"))
					feedTableEventResource(statEventModule, eventResource);
				else if(resourceType.equals("Group"))
					feedTableEventResource(statEventGroup, eventResource);
				else if(resourceType.equals("Room"))
					feedTableEventResource(statEventRoom, eventResource);
				else if(resourceType.equals("Staff"))
				{
					statStaff.setString(1, eventResource.get(1));
					statStaff.execute();
					
					feedTableEventResource(statEventStaff, eventResource);
				}
								
				line = p_in.readLine();
			}
		}
	}
	
	/**
	 * Fonction de remplissage de la db avec le fichier CSV.
	 * @param p_db La db.
	 * @param p_csvFile Le CSV.
	 */
	public void feedDb(Connection p_db, File p_csvFile) throws IOException, SQLException, Exception
	{
		CelcatSynchronizer.log.debug("Feeding database with [" + p_csvFile.getAbsolutePath() + "] csv file");
		
		BufferedReader in = new BufferedReader(new InputStreamReader(new FileInputStream(p_csvFile)));
		String line;
		while((line = in.readLine()) != null)
		{
			if(line.isEmpty())
				continue;
			if(!line.startsWith("CT_"))
				throw new IOException("Missing table name");
			
			// On skip le schéma de la table.
			in.readLine();
			
			CelcatSynchronizer.log.debug("Processing [" + line + "] table");

			// Switch sur le nom de la table.
			if(line.equals("CT_MODULE"))
				parseCsvTable(p_db.prepareStatement("INSERT INTO CT_MODULE (_module_id, _unique_name, _name, _academic_year, _deptName, _staff1Name, _staff2Name, _tag1, _tag2, _tag3, _weekly_target, _total_target, _schedulable, _www) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"), in, getClass().getDeclaredMethod("feedTableModule", PreparedStatement.class, ArrayList.class));
			else if(line.equals("CT_ROOM"))
				parseCsvTable(p_db.prepareStatement("INSERT INTO CT_ROOM (_room_id, _unique_name, _name, _deptName, _siteName, _area, _staff1Name, _staff2Name, _tag1, _tag2, _tag3, _schedulable, _auto_schedulable, _default_capacity, _charge, _tel, _www, _deaf_loop, _wheelchair_access, _exam_staff_capacity, _exam_student_capacity, _exam_layoutName, _exam_room_pref, _exam_room_usage, _exam_usage_pref, _exam_room, _exam_set_policy, _exam_set_pref) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"), in, getClass().getDeclaredMethod("feedTableRoom", PreparedStatement.class, ArrayList.class));
			else if(line.equals("CT_GROUP"))
				parseCsvTable(p_db.prepareStatement("INSERT INTO CT_GROUP (_group_id, _unique_name, _name, _academic_year, _deptName, _staff1Name, _staff2Name, _tag1, _tag2, _tag3, _weekly_target, _total_target, _target_size, _schedulable, _www) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"), in, getClass().getDeclaredMethod("feedTableGroup", PreparedStatement.class, ArrayList.class));
			else if(line.equals("CT_EVENT"))
				parseCsvTableEvent(p_db, in);
			else
			{
				// La table ne nous sert à rien, on la skip.
				CelcatSynchronizer.log.debug("Skipping it...");
				do
				{
					line = in.readLine();
				} while(line != null && !line.isEmpty());
			}
		}
	}
}
